import pymysql
from dbutils.pooled_db import PooledDB
from config.config import DB_CONFIG, PROJECT_CONFIG
import pandas as pd

pool = PooledDB(pymysql, blocking=True, host=DB_CONFIG["host"], user=DB_CONFIG["user"],
                passwd=DB_CONFIG["password"], database=DB_CONFIG["database"], port=DB_CONFIG["port"],
                charset=DB_CONFIG["charset"])
BaiduMap_API_KEY = PROJECT_CONFIG["BaiduMap_API_KEY"]


def from_name_to_address(schoolName, API_Key):
    # encoding:utf-8
    import requests

    # 接口地址
    url = "https://api.map.baidu.com/place/v2/search"

    # 此处填写你在控制台-应用管理-创建应用后获取的AK
    ak = API_Key

    params = {
        "query": schoolName,
        "tag": "学校",
        "region": "西安",
        "output": "json",
        "ak": ak,

    }

    response = requests.get(url=url, params=params)
    if response:
        # print(schoolName)
        print(response.json())
        return response.json()


def importSchoolInfo(df, API_Key):
    # print(df)
    conn = pool.connection()
    cursor = conn.cursor()
    for i in range(len(df)):
        print(i)
        school = df.iloc[i]
        schoolCode = school['学校代码']
        schoolName = school['高中学校']
        schoolType = school['学校类型']
        schoolLevel = school['办学等级']
        schoolLabel = school['备注']
        directToUnify = school['定转统分数线']
        acceptedScore = school['录取分数线']
        acceptedYear = 2023
        acceptedLine = school["普高线"]
        canLive = school["能否住宿"]
        schoolWeight = 1 #学校推荐权重
        schoolInfoResponse = from_name_to_address(schoolName, API_Key)
        schoolArea = schoolInfoResponse['results'][0]['area']
        schoolAddress = schoolInfoResponse['results'][0]['address']
        schoolLat = schoolInfoResponse['results'][0]['location']['lat'] - 0.006444
        schoolLng = schoolInfoResponse['results'][0]['location']['lng'] - 0.006618
        # 导入学校信息,当学校代码一致时，更新数据
        sql1 = f"insert into SchoolInfo(SchoolCode,SchoolName,SchoolLevel,SchoolType,SchoolArea,SchoolAddress,SchoolLabel,SchoolLat,SchoolLng,SchoolWeight,SchoolLiveIn)"
        sql1 += f" values('{schoolCode}','{schoolName}',{schoolLevel},{schoolType},'{schoolArea}','{schoolAddress}','{schoolLabel}',{schoolLat},{schoolLng},{schoolWeight},{canLive})"
        sql1 += f" on duplicate key update SchoolCode='{schoolCode}',SchoolName='{schoolName}',SchoolLevel={schoolLevel},SchoolType={schoolType},SchoolArea='{schoolArea}',SchoolAddress='{schoolAddress}',SchoolLabel='{schoolLabel}',SchoolLat={schoolLat},SchoolLng={schoolLng},SchoolWeight={schoolWeight},SchoolLiveIn={canLive}"
        #print(sql1)
        #cursor.execute(sql1)

        # 导入录取信息，适用于初次导入
        sql2 = f"insert into AcceptedInfo(SchoolCode,AcceptedYear,AcceptedScore,AcceptedLine)"
        sql2 += f" values('{schoolCode}','{acceptedYear}',{acceptedScore},{acceptedLine})"
        #print(sql2)
        #cursor.execute(sql2)

        #当学校代码和年份与新数据相同时更新录取信息，确保每年只有一个数据
        sql3=f"update AcceptedInfo set AcceptedYear={acceptedYear},AcceptedScore={acceptedScore},AcceptLine={acceptedLine},DirectToUnify={directToUnify} "
        sql3 += f" where SchoolCode='{schoolCode}' and AcceptedYear='{acceptedYear}'"
        # print(sql3)
        # cursor.execute(sql3)

    conn.commit()
    cursor.close()
    conn.close()
    print("导入学校信息结束")


if __name__ == '__main__':
    filepath = "../../信息/SchoolAndScore1.xlsx"
    df = pd.read_excel(filepath, sheet_name='2023',
                       dtype={'序号': str, '高中学校': str, '办学等级': str, '学校类型': str, '学校代码': str,
                              '普高线': float,
                              '录取分数线': float, '定转统分数线': float, '能否住宿': float, '备注': str})
    df.fillna('NULL', inplace=True)
    # 导入学校信息
    importSchoolInfo(df, BaiduMap_API_KEY)
    # from_name_to_address("西安市西航一中",BaiduMap_API_KEY)
